package cur.nature.tinyorm.core.proxy;

import com.alibaba.fastjson2.JSON;
import cur.nature.tinyorm.core.bean.PageBean;
import cur.nature.tinyorm.core.common.MBean;
import cur.nature.tinyorm.core.common.OrmUtil;
import cur.nature.tinyorm.core.template.DefaultJdbcTemplate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import java.util.*;
import java.util.stream.Collectors;

public class JdbcTemplateProxyBean extends AbstractTemplateProxy implements TemplateProxy {
    private static Logger LOG = LoggerFactory.getLogger(JdbcTemplateProxyBean.class);

    private String databaseType;
    private JdbcTemplate template;

    public JdbcTemplateProxyBean(DefaultJdbcTemplate defaultJdbcTemplate, String databaseType) {
        this.databaseType = databaseType;
        this.template = defaultJdbcTemplate;
        if ("oracle".equalsIgnoreCase(databaseType)) {
            super.setDoute("");
        } else if ("mysql".equalsIgnoreCase(databaseType)) {
            super.setDoute("`");
        }
    }

    public JdbcTemplate getTemplate() {
        return this.template;
    }

    public void setTemplate(JdbcTemplate template) {
        this.template = template;
    }

    public JdbcTemplateProxyBean(JdbcTemplate template) {
        this.template = template;
    }

    // 查询列表
    public Map<String, Object> queryMap(String sql, Map<String, Object> param) {
        List<Object> paramList = new ArrayList<>();
        // 组装参数
        if (param != null && !param.isEmpty()) {
            for (Map.Entry<String, Object> entry : param.entrySet()) {
                // 循环替换参数
                // 找不到则退出
                while (sql.contains("#{" + entry.getKey() + "}")) {
                    sql = sql.replaceAll("#\\{" + entry.getKey() + "}", " ? ");
                    paramList.add(entry.getValue());
                }
            }
        }
        // 查询
        LOG.info("sql: " + sql);
        LOG.info("params: " + JSON.toJSONString(paramList));
        return template.queryForMap(sql, paramList.toArray());
    }

    // 分页查询
    public <T> PageBean<T> queryForPage(String sql, PageBean<T> pagination, Class<T> resultType) throws Exception {
        return queryForPage(sql, null, pagination, resultType);
    }

    // 分页查询
    public <T> PageBean<T> queryForPage(String sql, PageBean<T> pagination,
                                        Map<String, Object> param, Class<T> resultType) throws Exception {
        // 组装参数
        MBean.M2<String, List<Object>> paramResult = parseParam(sql, param);
        sql = paramResult.v1;
        List<Object> paramList = paramResult.v2;
        return queryForPage(sql, paramList.toArray(), pagination, resultType);
    }

    // 分页查询
    public <T> PageBean<T> queryForPage(String sql, Object[] param, PageBean<T> pagination, Class<T> resultType) throws Exception {
        //获取记录条数
        String countSql = "select count(1) as count from (" + sql + ") temp";
        LOG.info("统计总数SQL: " + countSql);
        Long count = template.queryForObject(countSql, Long.class, param);
        LOG.info("row count: " + count);
        if (null == count || count == 0) {
            return pagination;
        }
        pagination.setTotal(count);
        pagination.setPages((count + pagination.getPageSize() - 1) / pagination.getPageSize());

        //查询数据列表
        List<Object> paramList = new ArrayList<>(Arrays.asList(param));
        String databaseType = null;
        try (Connection connection = template.getDataSource().getConnection()) {
            databaseType = connection.getMetaData().getDatabaseProductName();
        } catch (Exception ex) {
            LOG.error("conn err: ", ex);
        }
        if ("oracle".equalsIgnoreCase(databaseType)) {
            sql = "SELECT * FROM ( SELECT a.*, ROWNUM rnum FROM (" + sql + " ) a WHERE ROWNUM <= ?) WHERE rnum > ?";
            final long start = (pagination.getPageNum() - 1L) * pagination.getPageSize();
            paramList.add(start + pagination.getPageSize());
            paramList.add(start);

        } else if ("postgresql".equalsIgnoreCase(databaseType)) {
            sql += " limit ? offset ? ";
            paramList.add(pagination.getPageSize());
            paramList.add((pagination.getPageNum() - 1L) * pagination.getPageSize());

        } else {//默认mysql
            sql += " limit ? , ? ";
            paramList.add((pagination.getPageNum() - 1) * pagination.getPageSize());
            paramList.add(pagination.getPageSize());
        }
        List<Map<String, Object>> dataList = queryList(sql, paramList.toArray());
        List<T> data = parseList(dataList, resultType, null);
        pagination.setList(data);
        pagination.setSize(data.size());
        return pagination;
    }

    // 查询列表
    public <T> List<T> queryForListCust(String sql, Class<T> resultType) throws Exception {
        return queryForListCust(sql, null, resultType);
    }

    // 查询列表
    public <T> List<T> queryForListCust(String sql, Map<String, Object> param, Class<T> resultType) throws Exception {
        // 查询
        List<T> list = queryForListJdbc(sql, param, resultType);
        LOG.info("查询参数: " + JSON.toJSONString(param));
        return list;
    }

    // 查询列表
    public <T> List<T> queryForListJdbc(String sql, Map<String, Object> param, Class<T> resultType) throws Exception {
        LOG.info("查询SQL: " + sql);
        List<Map<String, Object>> dataList = queryList(sql, param);
        List<T> data = parseList(dataList, resultType, null);
        return data;
    }

    // 查询
    public <T> T queryForObjectCust(String sql, Class<T> resultType) throws Exception {
        List<T> list = queryForListCust(sql, resultType);
        if (list != null && list.size() > 1) {
            throw new Exception("结果集大于2行");
        }
        if (list != null && list.size() > 0) {
            T t = list.get(0);
            if (resultType == Map.class) {
                return t;
            }
            if (t instanceof Map) {
                for (Object value : ((Map) t).values()) {
                    return (T) value;
                }
            }
            return t;
        }

        return null;
    }

    // 查询列表
    public List<Map<String, Object>> queryList(String sql, Map<String, Object> param) {
        // 组装参数
        MBean.M2<String, List<Object>> paramResult = parseParam(sql, param);
        sql = paramResult.v1;
        List<Object> paramList = paramResult.v2;
        // 查询
        List<Map<String, Object>> resultList = queryList(sql, paramList.toArray());
        return resultList;
    }

    // 查询列表
    public List<Map<String, Object>> queryList(String sql, Object[] param) {
        // 查询
        LOG.info("sql: " + sql);
        LOG.info("params: " + JSON.toJSONString(param));
        List<Map<String, Object>> resultList = template.queryForList(sql, param);
        return resultList;
    }

    // 插入
    public int insert(Object obj, boolean isAllColumns) throws IllegalAccessException {
        // 查出字段名及字段值
        MBean.M3<Map<String, Object>, String, List<MBean.M2<String, Object>>> packReturn = packUpdateFields(obj, isAllColumns);
        Map<String, Object> valueMap = packReturn.v1; // 字段及字段值
        String tableName = packReturn.v2; // 表名
        List<MBean.M2<String, Object>> idColumnList = packReturn.v3; // 主键及值
        // 主楗
        if (idColumnList != null && idColumnList.size() > 0) {
            for (MBean.M2<String, Object> idColumn : idColumnList) {
                if (idColumn.v2 != null) {
                    valueMap.put(idColumn.v1, idColumn.v2);
                }
            }
        }

        // 分析
        List<String> columnList = new ArrayList();
        List<String> paramList = new ArrayList<>();
        List<Object> valueList = new ArrayList<>();
        for (Map.Entry<String, Object> entry : valueMap.entrySet()) {
            columnList.add(entry.getKey());
            paramList.add(" ? ");
            valueList.add(entry.getValue());
        }
        // 拼接sql
        String column = columnList.stream().collect(Collectors.joining(","));
        String param = paramList.stream().collect(Collectors.joining(","));
        String sql = "INSERT INTO " + tableName + " (" + column + ") VALUES (" + param + ")";
        // 执行sql
        LOG.info("sql: " + sql);
        LOG.info("params:" + JSON.toJSONString(valueMap));
        return template.update(sql, valueList.toArray());
    }

    // 插入
    public int insert(Object obj, String tableName, boolean isAllColumns) throws IllegalAccessException {
        // 查出字段名及字段值
        MBean.M3<Map<String, Object>, String, List<MBean.M2<String, Object>>> packReturn = packUpdateFields(obj, isAllColumns);
        Map<String, Object> valueMap = packReturn.v1; // 字段及字段值
        tableName = OrmUtil.isNotEmpty(tableName) ? (getDoute()+tableName+getDoute()) : packReturn.v2; // 表名
        List<MBean.M2<String, Object>> idColumnList = packReturn.v3; // 主键及值
        // 主楗
        if (idColumnList != null && idColumnList.size() > 0) {
            for (MBean.M2<String, Object> idColumn : idColumnList) {
                if (idColumn.v2 != null) {
                    valueMap.put(idColumn.v1, idColumn.v2);
                }
            }
        }

        // 分析
        List<String> columnList = new ArrayList();
        List<String> paramList = new ArrayList<>();
        List<Object> valueList = new ArrayList<>();
        for (Map.Entry<String, Object> entry : valueMap.entrySet()) {
            columnList.add(entry.getKey());
            paramList.add(" ? ");
            valueList.add(entry.getValue());
        }
        // 拼接sql
        String column = columnList.stream().collect(Collectors.joining(","));
        String param = paramList.stream().collect(Collectors.joining(","));
        String sql = "INSERT INTO " + tableName + " (" + column + ") VALUES (" + param + ")";
        // 执行sql
        LOG.info("sql: " + sql);
        LOG.info("params:" + JSON.toJSONString(valueMap));
        return template.update(sql, valueList.toArray());
    }

    // 批量插入
    public int[] batchInsert(List list) throws IllegalAccessException {
        return batchInsert(list, null);
    }

    // 批量插入
    public int[] batchInsert(List list, String tableName) throws IllegalAccessException {
        if (list == null || list.isEmpty()) {
            return new int[]{0};
        }

        String sql = null;
        List<String> columnList = new ArrayList<>();
        List<String> paramList = new ArrayList<>();
        List<Object[]> values = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Object obj = list.get(i);
            // 查出字段名及字段值
            MBean.M3<Map<String, Object>, String, List<MBean.M2<String, Object>>> packReturn = packUpdateFields(obj, true);
            Map<String, Object> valueMap = packReturn.v1; // 字段及字段值
            tableName = OrmUtil.isNotEmpty(tableName) ? (getDoute()+tableName+getDoute()) : packReturn.v2; // 表名
            List<MBean.M2<String, Object>> idColumnList = packReturn.v3; // 主键及值
            // 主楗
            if (idColumnList != null && !idColumnList.isEmpty()) {
                for (MBean.M2<String, Object> idColumn : idColumnList) {
                    if (idColumn.v2 != null) {
                        valueMap.put(idColumn.v1, idColumn.v2);
                    }
                }
            }
            // 分析
            List<Object> valueList = new ArrayList<>();
            for (Map.Entry<String, Object> entry : valueMap.entrySet()) {
                if (i == 0) {
                    columnList.add(entry.getKey());
                    paramList.add(" ? ");
                }
                valueList.add(entry.getValue());
            }
            values.add(valueList.toArray());
            if (i == 0) {
                // 拼接sql
                String column = String.join(",", columnList);
                String param = String.join(",", paramList);
                sql = (sql == null ? "INSERT INTO " + tableName + " (" + column + ") VALUES (" + param + ")" : sql);
            }
        }
        // 执行sql
        LOG.info("sql: " + sql);
        LOG.info("params:" + values.size());
        return template.batchUpdate(sql, values);
    }

    // 批量插入
    public int update(Object obj, String condition,
                      boolean isAllColumns, boolean updateById) throws IllegalAccessException {
        return update(obj, condition, isAllColumns, updateById, null);
    }

    // 更新
    public int update(Object obj, String condition,
                      boolean isAllColumns, boolean updateById, String tableName) throws IllegalAccessException {
        condition = (condition == null ? "" : condition);
        // 查出字段名及字段值
        MBean.M3<Map<String, Object>, String, List<MBean.M2<String, Object>>> packReturn = packUpdateFields(obj, isAllColumns);
        Map<String, Object> valueMap = packReturn.v1; // 字段及字段值
        tableName = OrmUtil.isNotEmpty(tableName) ? (getDoute()+tableName+getDoute()) : packReturn.v2; // 表名
        List<MBean.M2<String, Object>> idColumnList = packReturn.v3; // 主键及值
        // 分析
        List<String> fieldList = new ArrayList();
        List<Object> valueList = new ArrayList<>();
        for (Map.Entry<String, Object> entry : valueMap.entrySet()) {
            boolean present = idColumnList.stream().anyMatch(s -> s.v1.equalsIgnoreCase(entry.getKey()));
            if (present) {
                continue;
            }// 路过id
            fieldList.add(entry.getKey() + " = ? ");
            valueList.add(entry.getValue());
        }
        //将id 拼接在 where 里面
        if (idColumnList != null && idColumnList.size() > 0 && updateById) {
            for (MBean.M2<String, Object> idColumn : idColumnList) {
                condition += " and " + idColumn.v1 + " = ? ";
                valueList.add(idColumn.v2);
                LOG.info("key: " + idColumn.v1 + ", " + idColumn.v2);
            }
        }
        // 拼接sql
        String field = fieldList.stream().collect(Collectors.joining(","));
        String sql = "update " + tableName + " set " + field + " where 1=1 " + condition;
        // 执行sql
        LOG.info("sql: " + sql);
        LOG.info("params:" + JSON.toJSONString(valueMap));
        return template.update(sql, valueList.toArray());
    }

    // 更新
    public int[] batchUpdate(List list, boolean updateById, String tableName) throws IllegalAccessException {
        String condition = "";
        if (list == null || list.size() == 0) {
            return new int[]{0};
        }

        String sql = null;
        List<Object[]> values = new ArrayList<>();
        for (Object obj : list) {
            // 查出字段名及字段值
            MBean.M3<Map<String, Object>, String, List<MBean.M2<String, Object>>> packReturn = packUpdateFields(obj, true);
            Map<String, Object> valueMap = packReturn.v1; // 字段及字段值
            tableName = OrmUtil.isNotEmpty(tableName) ? getDoute()+tableName+getDoute() : packReturn.v2; // 表名
            List<MBean.M2<String, Object>> idColumnList = packReturn.v3; // 主键及值
            // 分析
            List<String> fieldList = new ArrayList();
//        List<String> paramList = new ArrayList<>();
            List<Object> valueList = new ArrayList<>();
            for (Map.Entry<String, Object> entry : valueMap.entrySet()) {
                boolean present = idColumnList.stream().anyMatch(s -> s.v1.equalsIgnoreCase(entry.getKey()));
                if (present) {
                    continue;
                }// 路过id
                fieldList.add(entry.getKey() + " = ? ");
                valueList.add(entry.getValue());
            }
            //将id 拼接在 where 里面
            if (idColumnList != null && idColumnList.size() > 0 && updateById) {
                for (MBean.M2<String, Object> idColumn : idColumnList) {
                    condition += " and " + idColumn.v1 + " = ? ";
                    valueList.add(idColumn.v2);
                    LOG.info("key: " + idColumn.v1 + ", " + idColumn.v2);
                }
            }
            values.add(valueList.toArray());
            // 拼接sql
            String field = fieldList.stream().collect(Collectors.joining(","));
            sql = (sql == null ? "update " + tableName + " set " + field + " where 1=1 " + condition : sql);
        }

        // 执行sql
        LOG.info("sql: " + sql);
        LOG.info("size:" + values.size());
        return template.batchUpdate(sql, values);
    }

    // 删除
    public <T> int deleteByIdList(List<Long> idList, Class<T> targetClass) {
        return deleteByIdList(idList, targetClass, null);
    }

    // 删除
    public <T> int deleteByIdList(List<Long> idList, Class<T> targetClass, String tableName) {
        // 查询缓存 v1:类, v2:表名, v3:id列名, v4:field->col
        MBean.M4<Class<?>, String, List<String>, Map<String, String>> classCache = getClassReflet(targetClass);
        tableName = OrmUtil.isNotEmpty(tableName) ? (getDoute()+tableName+getDoute()) : classCache.v2;
        String idColumn = classCache.v3.get(0);
        // 拼接sql
        String sql = "delete from " + tableName + " where " + idColumn + " in ( ? ) ";
        LOG.info("sql: {}", sql);
        // 执行
        return template.update(sql, idList.toArray());
    }

    // 删除
    public <T> int deleteByCond(String cond, Class<T> targetClass) {
        // 查询缓存 v1:类, v2:表名, v3:id列名, v4:field->col
        MBean.M4<Class<?>, String, List<String>, Map<String, String>> classCache = getClassReflet(targetClass);
        String tableName = classCache.v2;
        // 拼接sql
        String sql = "delete from " + tableName + " where 1=1 " + cond;
        LOG.info("sql: " + sql);
        // 执行
        return template.update(sql);
    }

    // 根据id查询
    public <T> T queryById(Class<T> targetClass, Long id) throws Exception {
        // 查询缓存 v1:类, v2:表名, v3:id列名, v4:field->col
        MBean.M4<Class<?>, String, List<String>, Map<String, String>> classCache = getClassReflet(targetClass);
        String tableName = classCache.v2;
        Collection<String> columns = classCache.v4.values();
        String idColumn = classCache.v3.get(0);
        // 拼接sql
        String column = columns.stream().collect(Collectors.joining(","));
        String sql = "select " + column + " from " + tableName + " where " + idColumn + " = " + id;
        // 执行
        Map<String, Object> map = template.queryForMap(sql);
        if (targetClass == Map.class) {
            return (T) map;
        }
        return parseObject(map, targetClass, null, null);
    }

    // 根据id列表查询
    public <T> List<T> queryByIdList(Class<T> targetClass, List<Long> idList) throws Exception {
        // 查询缓存 v1:类, v2:表名, v3:id列名, v4:field->col
        MBean.M4<Class<?>, String, List<String>, Map<String, String>> classCache = getClassReflet(targetClass);
        String tableName = classCache.v2;
        Collection<String> columns = classCache.v4.values();
        String idColumn = classCache.v3.get(0);

        // 拼接sql
        String column = columns.stream().collect(Collectors.joining(","));
        String sql = "select " + column + " from " + tableName + " where " + idColumn + " in ( " + OrmUtil.convertArrayToStr(idList) + " ) ";
        // 查询
        Map<String, Object> param = new HashMap<>();
        List<Map<String, Object>> mapList = queryList(sql, param);
        if (mapList != null && mapList.size() > 0) {
            return parseList(mapList, targetClass, null);
        }
        return null;
    }

    // 查询
    public <T> List<T> queryByCond(String condition, Class<T> targetClass) throws Exception {
        // 查询缓存 v1:类, v2:表名, v3:id列名, v4:field->col
        MBean.M4<Class<?>, String, List<String>, Map<String, String>> classCache = getClassReflet(targetClass);
        String tableName = classCache.v2;
        Collection<String> columns = classCache.v4.values();
        // 拼接sql
        String column = columns.stream().collect(Collectors.joining(","));
        String sql = "select " + column + " from " + tableName + (OrmUtil.isNotEmpty(condition) ? " where 1=1 " + condition : "");
        // 查询
        List<Map<String, Object>> mapList = queryList(sql, (Map<String, Object>) null);
        if (mapList != null && mapList.size() > 0) {
            return parseList(mapList, targetClass, null);
        }
        return null;
    }

    // 查询
    public <T> List<T> queryByCond(String condition, Map<String, Object> param, Class<T> targetClass) throws Exception {
        // 查询缓存 v1:类, v2:表名, v3:id列名, v4:field->col
        MBean.M4<Class<?>, String, List<String>, Map<String, String>> classCache = getClassReflet(targetClass);
        String tableName = classCache.v2;
        Collection<String> columns = classCache.v4.values();
        // 拼接sql
        String column = columns.stream().collect(Collectors.joining(","));
        String sql = "select " + column + " from " +tableName + (OrmUtil.isNotEmpty(condition) ? " where 1=1 " + condition : "");
        // 查询
        List<Map<String, Object>> mapList = queryList(sql, param);
        if (mapList != null && mapList.size() > 0) {
            return parseList(mapList, targetClass, null);
        }
        return null;
    }

    // 查询
    public <T> List<T> queryByCond(String condition, Object[] param, Class<T> targetClass) throws Exception {
        // 查询缓存 v1:类, v2:表名, v3:id列名, v4:field->col
        MBean.M4<Class<?>, String, List<String>, Map<String, String>> classCache = getClassReflet(targetClass);
        String tableName = classCache.v2;
        Collection<String> columns = classCache.v4.values();
        // 拼接sql
        String column =columns.stream().collect(Collectors.joining(","));
        String sql = "select " + column + " from " + tableName + (OrmUtil.isNotEmpty(condition) ? " where 1=1 " + condition : "");

        // 查询
        List<Map<String, Object>> mapList = queryList(sql, param);
        if (mapList != null && mapList.size() > 0) {
            return parseList(mapList, targetClass, null);
        }
        return null;
    }

    // 查询
    public <T> Long countByCond(String condition, Class<T> targetClass) {
        // 查询缓存 v1:类, v2:表名, v3:id列名, v4:field->col
        MBean.M4<Class<?>, String, List<String>, Map<String, String>> classCache = getClassReflet(targetClass);
        String tableName = classCache.v2;
        // 拼接sql
        String sql = "select count(0) from " + tableName + " where 1=1 " + condition;
        // 查询
        Long result = template.queryForObject(sql, Long.class);
        return result;
    }

}
